package com.jr.dao.impl;

import com.jr.dao.SupplierwhitelistDao;
import com.jr.pojo.Enterprise;
import com.jr.pojo.Supplierblacklist;
import com.jr.pojo.Supplierwhitelist;
import com.jr.util.DBHelper;
import com.jr.util.PageHelper;
import com.jr.vo.EnterVo;
import com.jr.vo.WhiteEnter;
import com.mysql.cj.util.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDate;
import java.util.*;

public class ISupplierWhitelistDaoImpl implements SupplierwhitelistDao {
    DBHelper db = new DBHelper();

    /*分页*/
    @Override
    public ArrayList<WhiteEnter> selectSupplierWhiteList(WhiteEnter we, PageHelper ph,String myentername) {
                       /*   selectWhiteEnterprise

        排列序号之前的原版*/
        StringBuilder sb = new StringBuilder("SELECT\n" +
                "    sw.supplierid                 AS id,\n" +
                "    supplier_e.entername  AS entername,\n" +
                "    u.username            AS username,\n" +
                "    u.phone               AS phone,\n" +
                "    supplier_e.email      AS email,\n" +
                "    sw.supplierlevel      AS supplierlevel,\n" +
                "    sw.updatedate         AS updatedate\n" +
                "FROM supplierwhitelist sw\n" +
                "JOIN enterprise e          ON sw.enterid   = e.id\n" +
                "JOIN enterprise supplier_e ON sw.supplierid = supplier_e.id\n" +
                "JOIN users u               ON u.entername = supplier_e.entername\n" +
                "WHERE e.entername = ? ");

       /* SELECT
    ROW_NUMBER() OVER (ORDER BY sw.id) as sequence_id,
    supplier_e.`entername` as supplier_name,
    u.`phone`,
    u.`username`,
    supplier_e.`email` as supplier_email,
    sw.`supplierlevel`,
    sw.`updatedate`
FROM enterprise e
INNER JOIN supplierwhitelist sw ON e.`id` = sw.`enterid`
INNER JOIN enterprise supplier_e ON sw.`supplierid` = supplier_e.`id`
INNER JOIN users u ON supplier_e.`entername` = u.`entername`
WHERE e.`entername` = '科技有限公司'
ORDER BY sequence_id;*/
        LinkedList list = new LinkedList();
        list.add(myentername);
        if (we != null) {
            if (!StringUtils.isNullOrEmpty(we.getEntername())) {
                sb.append(" AND supplier_e.entername LIKE ?");
                list.add(we.getEntername());
            }
            if (we.getSupplierlevel() != null && !we.getSupplierlevel().equals("")) {// && Integer.parseInt(we.getSupplierlevel())!=-1
                sb.append(" AND sw.supplierlevel =  ?  ");
                list.add(we.getSupplierlevel());
            }
        }
        System.out.println(list+"dao层集合");
        // 添加分页
        sb.append("LIMIT ?, ?");
        int index = (ph.getPageIndex() - 1) * ph.getShowNumber();
        if (index < 0) {
            index = 0;
        }
        list.add(index);
        list.add(ph.getShowNumber());
        list.get(0);

        System.out.println(ph.getListIndex()+"dao层的ph。listindex");
        System.out.println(ph+"dao层ph对象");
        // 如果需要返回包含企业信息的对象，可以创建对应的VO类
        return db.dtQuery(sb.toString(), WhiteEnter.class, list.toArray());
    }

    @Override
    public int selectSupplierWhiteListCount(WhiteEnter we ,String nowentername) {
        /*当前账户所在企业名称*/
        StringBuilder sb = new StringBuilder("SELECT\n" +
                " COUNT(*)\n" +
                "                FROM supplierwhitelist sw \n" +
                "                JOIN enterprise e          ON sw.enterid   = e.id \n" +
                "                JOIN enterprise supplier_e ON sw.supplierid = supplier_e.id \n" +
                "                JOIN users u               ON u.entername = supplier_e.entername \n" +
                "                WHERE e.entername =  ?");
        /**/
        LinkedList list = new LinkedList();
        list.add(nowentername);  // 首先添加nowentername参数
        if (we != null && we.getSupplierlevel() != null && !we.getSupplierlevel().equals("")) {
            sb.append(" AND sw.supplierlevel=?");
            list.add(we.getSupplierlevel());
        }
        if(we.getEntername()!=null&&!we.getEntername().equals("")){
            sb.append(" AND supplier_e.entername LIKE ?");  // 修改为供应商名称搜索
            list.add("%"+we.getEntername()+"%");
        }
        Connection con = db.getCon();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = con.prepareStatement(sb.toString());
            if (list.size() > 0) {
                for (int i = 0; i < list.size(); i++) {
                    ps.setObject(i + 1, list.get(i));
                }
            }
            rs = ps.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            db.closeAll(con, ps, rs);
        }
        return 0;
    }

    @Override
    /*查询出满足要求的企业名称*/
    public Map<Integer, String> selectWhiteEnterprise(String entername) {
        /*根据当前用户所在的企业名称去查询可以增加的企业的列表*/
        String sql = "SELECT e.entername,e.id \n" +
                "FROM enterprise e \n" +
                "WHERE e.entername != ? \n" +
                "  AND e.entername NOT IN (\n" +
                "    SELECT e2.entername\n" +
                "    FROM supplierBlacklist s \n" +
                "    INNER JOIN enterprise e2 ON s.supplierId = e2.id\n" +
                "    WHERE s.enterId = (\n" +
                "      SELECT e3.id \n" +
                "      FROM enterprise e3 \n" +
                "      WHERE e3.enterName = ?\n" +
                "    )\n" +
                "  )";
        ArrayList<EnterVo> enterpriseList = db.dtQuery(sql, EnterVo.class, entername, entername);
        System.out.println(db.dtQuery(sql,EnterVo.class,entername,entername)+"dao层通过语句获得的集合");
        Map<Integer, String> enterpriseMap = new HashMap<>();

        for (EnterVo enterprise : enterpriseList) {
            enterpriseMap.put(enterprise.getId(), enterprise.getEntername());//getEnterName
        }
        return enterpriseMap;
       //return  db.dtQuery(sql,Enterprise.class,entername,entername);
    }
    @Override
    /*先获得可添加的企业名称 也需要查询企业id 但是不需要显示出来 但是后面添加会用
    * 根据企业id 添加 也就是说需要调用预估根据名称查id 或者直接添加id*/
    public int insertWhiteEnterByName(String nowendername, String  addendername, String level, LocalDate date) {
        //参数应该是当前企业名称 和添加的供货商的企业名称  第一个？是当前企业名称  第二个？是供货商企业名称
        String sql = "INSERT INTO supplierwhitelist (enterid, supplierid, supplierlevel, updatedate)\n" +
                "VALUES (\n" +
                "    (SELECT id FROM enterprise WHERE entername = ?),\n" +
                "    (SELECT id FROM enterprise WHERE entername = ?),\n" +
                "    ?,  \n" +
                "    ?\n" +
                " )";
       /* System.out.println(sql+"....ISupplierWhitelistDaoImpl");*/
        /*insert into supplierwhitelist(enterid,supplierlevel,updatedate) values(null,?,null,?,?)*/
        return db.dtUpdate(sql,nowendername,addendername,level,date);/*更新  update不用映射 */
        /*时间获取系统当前时间加入数据库 其他两项是自增*/
    }

  /*  @Override
    *//*我要获得 白名单中供应商的id*//*
    public int selectWhiteIDByNAme(String proname) {
        String sql = "select * from enterprise where entername=? ";
       ArrayList<Enterprise> list =  db.dtQuery(sql,Enterprise.class,proname);
        return list.size();
    }*/
    /*先添加再删除*/
  @Override
  public int insertToBlackEnter(Integer enterid, Integer whiteid,  String resion,LocalDate date) {
      String sql = "INSERT INTO supplierBlacklist (id,enterid, supplierid, reason, updatedate)\n" +
              "VALUES (null,?,?,?,?);";
      return  db.dtUpdate(sql,enterid,whiteid,resion,date);
  }

    @Override
    public int selectWhiteInBlack(Integer nowid ,Integer blackid) {
      /*判断要加入的白名单是否在黑名单中已经存在*/
        String sql="select * from supplierblacklist where enterid=? and supplierid=?";
        return db.dtQuery(sql, Supplierblacklist.class,nowid,blackid).size();
    }
    @Override
    public int delWhiteEnter(Integer enterid, Integer whiteid) {
        /*企业的id 供应商的id 因为要删除指定供应商至黑名单*/
        /*企业的话就是当前 用户所在企业的id */
        String sql  = " DELETE FROM supplierwhitelist  WHERE enterid = ? AND supplierid = ? ";
        return  db.dtUpdate(sql,enterid,whiteid);
    }





}
